package cn.jh.clearing.repository;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import cn.jh.clearing.pojo.BrandWithdrawRebate;
import cn.jh.clearing.pojo.PaymentOrder;

@Repository
public interface PaymentOrderRepository extends JpaRepository<PaymentOrder,String>,JpaSpecificationExecutor<PaymentOrder>{

	@Query("select paymentOrder from  PaymentOrder paymentOrder where paymentOrder.ordercode=:ordercode")
	PaymentOrder findPaymentOrderByCode(@Param("ordercode") String ordercode);

	@Query("select paymentOrder from  PaymentOrder paymentOrder where paymentOrder.id=:orderId AND paymentOrder.ordercode like :ordercode% ")
	PaymentOrder findPaymentOrderByIdAndCode(@Param("orderId") int orderId, @Param("ordercode") String ordercode);

	@Query("select paymentOrder from  PaymentOrder paymentOrder where paymentOrder.outMerOrdercode=:outMerOrdercode")
	PaymentOrder findPaymentOrderByOutCode(@Param("outMerOrdercode") String ordercode);
	
	@Query("select paymentOrder from  PaymentOrder paymentOrder where paymentOrder.thirdOrdercode=:thirdcode")
	PaymentOrder findPaymentOrderByThirdCode(@Param("thirdcode") String thirdcode);

	@Query("select paymentOrder from  PaymentOrder paymentOrder where paymentOrder.ordercode=:ordercode and paymentOrder.status =:status")
	PaymentOrder findPaymentOrderByCodeAndStatus(@Param("ordercode") String ordercode, @Param("status") String status);

	@Modifying
	@Query("update PaymentOrder paymentOrder set paymentOrder.thirdOrdercode=:thirdOrdercode where paymentOrder.ordercode=:ordercode")
	void updatePaymentThirdcodeByOrdercode(@Param("thirdOrdercode") String thirdOrdercode, @Param("ordercode") String ordercode);

	@Modifying
	@Query("update PaymentOrder paymentOrder set paymentOrder.autoClearing=:autoclearing where paymentOrder.ordercode=:ordercode")
	void updateAutoClearingByOrdercode(@Param("autoclearing") String autoclearing, @Param("ordercode") String ordercode);

	@Query(value = "select paymentOrder from PaymentOrder paymentOrder where paymentOrder.type  = '2' and paymentOrder.channelTag != 'JIEFUBAO' and paymentOrder.status = '1' and paymentOrder.createTime >= :pridate and paymentOrder.createTime < :curdate")
	List<PaymentOrder>  findBrandWithdrawRebate(@Param("pridate") Date pridate, @Param("curdate") Date curdate);
	
	@Query(value = "select paymentOrder from PaymentOrder paymentOrder where paymentOrder.type  = '0' and paymentOrder.channelTag != 'JIEFUBAO' and paymentOrder.autoClearing='1'  and paymentOrder.status = '1' and paymentOrder.createTime >= :pridate and paymentOrder.createTime < :curdate")
	List<PaymentOrder>  findBrandWithdrawclearRebate(@Param("pridate") Date pridate, @Param("curdate") Date curdate);

	@Query("select sum(paymentOrder.realAmount) from  PaymentOrder paymentOrder where paymentOrder.type in (:type) and paymentOrder.status in (:status)  and paymentOrder.userid=:userid and paymentOrder.autoClearing=:autoClearing")
	BigDecimal findsumPaymentOrder(@Param("userid") long userid, @Param("type") String[] type, @Param("status") String[] status, @Param("autoClearing") String autoClearing);
	
	@Query("select paymentOrder from  PaymentOrder paymentOrder where paymentOrder.type in (:type) and paymentOrder.status in (:status)  and paymentOrder.userid=:userid and paymentOrder.descCode=:desccode and paymentOrder.createTime>=:startTimeDate and paymentOrder.createTime < :endTimeDate")
	List<PaymentOrder> findsumPaymentOrderByDescCode(@Param("userid") long userid, @Param("type") String[] type, @Param("status") String[] status, @Param("desccode") String desccode, @Param("startTimeDate") Date startTimeDate, @Param("endTimeDate") Date endTimeDate);

	@Query("select sum(paymentOrder.realAmount) from  PaymentOrder paymentOrder where paymentOrder.type in (:type) and paymentOrder.status in (:status)  and paymentOrder.userid=:userid and paymentOrder.autoClearing=:autoClearing and paymentOrder.createTime>=:startTimeDate and paymentOrder.createTime < :endTimeDate")
	BigDecimal findsumPaymentOrder(@Param("userid") long userid, @Param("type") String[] type, @Param("status") String[] status, @Param("autoClearing") String autoClearing, @Param("startTimeDate") Date startTimeDate, @Param("endTimeDate") Date endTimeDate);
	
	@Query("select count(*)  from  PaymentOrder paymentOrder where paymentOrder.type in (:type) and paymentOrder.status in (:status)  and paymentOrder.userid=:userid and paymentOrder.autoClearing=:autoClearing and paymentOrder.createTime>=:startTimeDate and paymentOrder.createTime < :endTimeDate")
	int findsumPaymentOrdercount(@Param("userid") long userid, @Param("type") String[] type, @Param("status") String[] status, @Param("autoClearing") String autoClearing, @Param("startTimeDate") Date startTimeDate, @Param("endTimeDate") Date endTimeDate);
	
	@Query("select sum(paymentOrder.realAmount) from  PaymentOrder paymentOrder where paymentOrder.type in (:type) and paymentOrder.status in (:status)  and paymentOrder.brandid=:brand and paymentOrder.autoClearing in (:autoClearing) and paymentOrder.createTime>=:startTimeDate and paymentOrder.createTime < :endTimeDate")
	BigDecimal findsumPaymentOrderBrand(@Param("brand") long brand, @Param("type") String[] type, @Param("status") String[] status, @Param("autoClearing") String[] autoClearing, @Param("startTimeDate") Date startTimeDate, @Param("endTimeDate") Date endTimeDate);
	
	@Query("select count(*)  from  PaymentOrder paymentOrder where paymentOrder.type in (:type) and paymentOrder.status in (:status)  and paymentOrder.brandid=:brand and paymentOrder.autoClearing in (:autoClearing) and paymentOrder.createTime>=:startTimeDate and paymentOrder.createTime < :endTimeDate")
	int findsumPaymentOrderBrandcount(@Param("brand") long brand, @Param("type") String[] type, @Param("status") String[] status, @Param("autoClearing") String[] autoClearing, @Param("startTimeDate") Date startTimeDate, @Param("endTimeDate") Date endTimeDate);
	
	@Query("select sum(paymentOrder.realAmount) from  PaymentOrder paymentOrder where paymentOrder.type in (:type) and paymentOrder.status in (:status)  and  paymentOrder.autoClearing in (:autoClearing) and paymentOrder.createTime>=:startTimeDate and paymentOrder.createTime < :endTimeDate")
	BigDecimal findsumPaymentOrderPlatform(@Param("type") String[] type, @Param("status") String[] status, @Param("autoClearing") String[] autoClearing, @Param("startTimeDate") Date startTimeDate, @Param("endTimeDate") Date endTimeDate);
	
	@Query("select count(*)  from  PaymentOrder paymentOrder where paymentOrder.type in (:type) and paymentOrder.status in (:status)  and  paymentOrder.autoClearing in (:autoClearing) and paymentOrder.createTime>=:startTimeDate and paymentOrder.createTime < :endTimeDate")
	int findsumPaymentOrderPlatformcount(@Param("type") String[] type, @Param("status") String[] status, @Param("autoClearing") String[] autoClearing, @Param("startTimeDate") Date startTimeDate, @Param("endTimeDate") Date endTimeDate);

	@Query("select paymentOrder.ordercode from  PaymentOrder paymentOrder where paymentOrder.brandid =:brandid and paymentOrder.type in (:type)  and paymentOrder.status in (:status)  ")
	String[] findAllPaymentOrderordercodesbyBrandid(@Param("brandid") Long brandid, @Param("type") String[] type, @Param("status") String[] status);

	PaymentOrder findByOrdercode(String orderCode);
	
	//依据用户phone获取用户该时间段内所有order
	@Query("from  PaymentOrder paymentOrder where paymentOrder.phone=:phone and paymentOrder.brandid=:brandid and paymentOrder.createTime>=:startTimeDate and paymentOrder.createTime<=:endTimeDate")
	List<PaymentOrder> findOrderByphoneAndbrandid(@Param("phone") String phone, @Param("brandid") long brandid, @Param("startTimeDate") Date startTimeDate, @Param("endTimeDate") Date endTimeDate, Pageable pageable);
	
	//根据条件查询出userId
	@Query("select paymentOrder.userid from PaymentOrder paymentOrder where paymentOrder.status = 1 and paymentOrder.brandid =:brandId and paymentOrder.type = 0 and paymentOrder.userid not in (select autoRebateHistory.userId from AutoRebateHistory autoRebateHistory where autoRebateHistory.rebateConfigId =:autoRebateConfigId) group by paymentOrder.userid having sum(paymentOrder.amount) >=:limitAmount")
	List<Long> queryUserIdsByAmount(@Param("brandId") Long brandId, @Param("limitAmount") BigDecimal limitAmount, @Param("autoRebateConfigId") Long autoRebateConfigId);

	//调用此方法获取待结算订单
	@Query("select paymentOrder from PaymentOrder paymentOrder where paymentOrder.userid=:userid and paymentOrder.status in (:status)")
	List<PaymentOrder> findOrderByUseridAndStatus(@Param("userid") Long userid, @Param("status") String[] status);

	@Query("select paymentOrder.userid,sum(paymentOrder.realAmount) from  PaymentOrder paymentOrder where paymentOrder.type in (:type) and paymentOrder.status in (:status) and paymentOrder.autoClearing=:autoClearing and paymentOrder.userid in (:userIds) group by paymentOrder.userid")
	List<Object[]> findSumByUserIds(@Param("userIds") long[] userIds, @Param("type") String type, @Param("status") String status, @Param("autoClearing") String autoClearing);
	
	
	@Modifying
	@Query("update PaymentOrder set remark=:remark where ordercode=:ordercode")
	void updateOrderCodeMsg(@Param("ordercode") String ordercode, @Param("remark") String remark);
}
